Release 10.1A: OpenEdge Development:
ProDataSets

Testing the Order ProDataSet

You can now put these statements together to create a procedure that defines, fills, and displays data from the ProDataSet dsOrder. Procedure fillDSOrder.p follows this process:

Includes the temp-table definitions and the ProDataSet definition you’ve already seen.
Defines the queries for the ttOrder table and the ttItem table (the second one is used later).
Defines Data-Sources for all three temp-tables.
Prepares the top-level query for the ttOrder table to bring in Order number 1.
Attaches all three Data-Sources to the ProDataSet buffers.
Executes the FILL method on the ProDataSet handle.
Detaches the Data-Sources from the ProDataSet buffers, as shown in the following example:

/* fillDSOrder.p -- Test procedure for an Order Dataset for OpenEdge 10 */ {dsOrderTT.i} {dsOrder.i} DEFINE QUERY qOrder FOR Order, Customer, SalesRep. DEFINE QUERY qItem FOR ITEM. DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder Order KEYS (OrderNum),        Customer KEYS (CustNum), SalesRep KEYS (SalesRep). DEFINE DATA-SOURCE srcOline FOR OrderLine KEYS (OrderNum). DEFINE DATA-SOURCE srcItem FOR ITEM KEYS (ItemNum). QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum =1, " + "FIRST Customer OF Order, FIRST SalesRep OF Order"). BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE, "Customer.Name,CustName"). BUFFER ttOline:ATTACH-DATA-SOURCE(DATA-SOURCE srcOline:HANDLE). BUFFER ttItem:ATTACH-DATA-SOURCE(DATA-SOURCE srcItem:HANDLE). DATASET dsOrder:FILL(). BUFFER ttOrder:DETACH-DATA-SOURCE(). BUFFER ttOline:DETACH-DATA-SOURCE(). BUFFER ttItem:DETACH-DATA-SOURCE().

At this point Order number 1, its OrderLines, and their Items, are all in the ProDataSet temp-tables.

You can verify this simply by displaying some fields from them. For example:

FOR EACH ttOrder: DISPLAY ttOrder.OrderNum ttOrder.OrderDate ttOrder.CustName FORMAT "X(15)" ttOrder.RepName FORMAT "X(15)". END. FOR EACH ttOline: DISPLAY ttOline.OrderNum ttOline.LineNum. END. FOR EACH ttItem: DISPLAY ttItem.ItemNum ttItem.ItemName. END.

When you run the procedure, you see the results of the DISPLAY statements—the Order:

All of its OrderLines:

And all the Items used on any of those OrderLines:

This looks fine, but in fact, you were lucky you didn’t get an error when you ran it. Try changing the selection for the Order query to fill all Orders less than 10, as shown in this code snippet:

QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum < 10, " + "FIRST Customer OF Order, FIRST SalesRep OF Order").

This will also fill all their OrderLines and all the Items used on any of those OrderLines. Run the procedure and display the Orders, OrderLines, and finally the Items:

Some of the Item records, such as Item 54, are represented more than once. This is because they are used in more than one OrderLine. You probably don’t want them in your ttItem table more than once. Since MERGE is the default FILL-MODE, why didn’t Progress eliminate the duplicates?

The answer is that you didn’t have an index on the ttItem table. Progress eliminates duplicates in a ProDataSet temp-table by relying on the internal indexing mechanism. Because this temp-table is not defined to be LIKE a database table, it does not inherit the Item table’s indexes automatically. For Progress to eliminate duplicate Items from this temp-table should they occur, you need a unique index on the ItemNum field. Add an index to the temp-table definition in the include file dsOrderTT.i, as shown:

DEFINE TEMP-TABLE ttItem FIELD ItemNum LIKE ITEM.ItemNum FIELD ItemName LIKE ITEM.ItemName FIELD Price LIKE ITEM.Price FIELD Weight LIKE ITEM.Weight FIELD OnHand LIKE ITEM.OnHand FIELD OnOrder LIKE ITEM.OnOrder INDEX ItemNum IS UNIQUE ItemNum.

Run the procedure again and the duplicate records are gone from the ttItem table. The Items now come out in order as well, because of the index. This is the end of the display:

Design tip: If there is a chance that a FILL operation might create duplicate records in a temp-table that you want eliminated, you must either define or inherit from the database schema a unique index for the temp-table, as done here for the ttItem table.